if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_kq_user_case')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_kq_user_case
GO
CREATE PROCEDURE bd_proc_kq_user_case
	@yearMonth VARCHAR(max)
as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;


-- 定义返回受影响行数结果
	declare @result int = 0;

	--  异常扑捉机制
		BEGIN TRY

			--创建临时表
			CREATE TABLE #temp(
				user_serial INT,
				year_month VARCHAR(max),
				day VARCHAR(max),
				dayValue VARCHAR(max)
			)

		-- 开启事务
			BEGIN TRAN

				--删除当前月份的记录
				delete from bd_kq_user_case where year_month = @yearMonth;

				--添加用户ID,日期,编号,时长到临时表
				INSERT INTO #temp (user_serial,year_month,day,dayValue)
				SELECT
					user_serial,
					@yearMonth,
					'day' + CONVERT (VARCHAR(7),DATEPART(dd,card_date)),
					case
						when ot_type_id = 1 and ot_normal_hours>0 then 'OT1'+CONVERT(VARCHAR,CONVERT(numeric(20,1),ot_normal_hours))
						when ot_type_id = 2 and ot_weekend_hours>0 then 'OT2'+CONVERT(VARCHAR,CONVERT(numeric(20,1),ot_weekend_hours))
						when ot_type_id = 3 and ot_holiday_hours>0 then 'OT3'+CONVERT(VARCHAR,CONVERT(numeric(20,1),ot_holiday_hours))
						when ot_type_id = 4 and ot_to_rest_hours>0 then '转调休'+CONVERT(VARCHAR,CONVERT(numeric(20,1),ot_to_rest_hours))
						else ''
					end+
					case
						when status<=3 and status>=0 then '正常8.0'
						else ''
					end+
					case
						when shift_middle_count>0 then '中班'+CONVERT(VARCHAR,CONVERT(numeric(20,1),shift_middle_count))
						else ''
					end+
					case
						when shift_night_count>0 then '夜班'+CONVERT(VARCHAR,CONVERT(numeric(20,1),shift_night_count))
						else ''
					end
				FROM
					bd_kq_timecard
				WHERE
					CONVERT(VARCHAR(7),card_date,120) = @yearMonth;

				--处理假类数据
				INSERT INTO bd_kq_user_case
				SELECT
					user_serial,
					year_month,
					day1,
					day2,
					day3,
					day4,
					day5,
					day6,
					day7,
					day8,
					day9,
					day10,
					day11,
					day12,
					day13,
					day14,
					day15,
					day16,
					day17,
					day18,
					day19,
					day20,
					day21,
					day22,
					day23,
					day24,
					day25,
					day26,
					day27,
					day28,
					day29,
					day30,
					day31
				FROM (
					SELECT *
					FROM dbo.#temp
				) t
				PIVOT (
					max(dayValue)
					FOR day IN (
							day1,
							day2,
							day3,
							day4,
							day5,
							day6,
							day7,
							day8,
							day9,
							day10,
							day11,
							day12,
							day13,
							day14,
							day15,
							day16,
							day17,
							day18,
							day19,
							day20,
							day21,
							day22,
							day23,
							day24,
							day25,
							day26,
							day27,
							day28,
							day29,
							day30,
							day31
					)
				) p;

				-- 出差  00101
				-- 公出  00102
				-- 培训  00103
				-- 公假  00104
				-- 调休  00105
				-- 年假  00106
				-- 病假  00107
				-- 事假  00108
				-- 婚假  00109
				-- 丧假  00110
				-- 探亲  00111
				-- 工伤  00112
				-- 放班  00113
				-- 产前假  00114
				-- 产假  00115
				-- 产后留休  00116
				-- 陪产假  00117
				-- 旷工   00118

				--添加没有请假类的人员信息
				INSERT INTO bd_kq_user_case (user_serial,year_month)
				select user_serial,@yearMonth from dt_user where user_type !=51
				and user_serial not in (
					select
						user_serial
					from bd_kq_timecard
					where  CONVERT(VARCHAR(7),card_date,120) = @yearMonth);

				set @result = @@ROWCOUNT;


		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
			ROLLBACK TRAN;
		END CATCH



--  清除临时表
	DROP TABLE #temp;
	select @result as result

-- 打开打印受影响行数
SET NOCOUNT OFF;
END;
